package com.nebula.dao.impl;

import com.nebula.entity.Salary;
import com.nebula.dao.Database;
import com.nebula.dao.ISalaryDao;
import com.nebula.vo.SalaryAnalyzeDGVo;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author CcccX
 * @create 2019-01-21 14:27
 */
public class SalaryDaoImpl extends BaseDaoImpl<Salary> implements ISalaryDao {
	@Override
	public BigDecimal getTotalSalary(String id, Object... conditions) {
		String sql = "select sum(salary) from salary where user_id = ?";
		sql = handle(sql, conditions);
		BigDecimal dc = BigDecimal.valueOf(0);
		ResultSet rs = (ResultSet) Database.excute(sql, id);
		try {
			if (rs != null) {
				while (rs.next()) {
					dc = rs.getBigDecimal(1);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dc;
	}

	@Override
	public List<SalaryAnalyzeDGVo> findSalaryAnalyzeByUserIdAndPage(String id, int page, int size) {
		String sql = "select issue_year,sum(salary) salary,count(issue_month) issue_month,sum(salary)/count"
				+ "(issue_month) avg_salary from salary where user_id = ? group by issue_year limit "
				+ (page - 1) * size + ", " + size;

		List<SalaryAnalyzeDGVo> dgVos = new ArrayList<SalaryAnalyzeDGVo>();
		ResultSet rs = (ResultSet) Database.excute(sql, new Object[] { (Object) id });
		NumberFormat nf = NumberFormat.getCurrencyInstance();

		try {
			while (rs.next()) {
				SalaryAnalyzeDGVo salaryAnalyzeDGVo = new SalaryAnalyzeDGVo();
				salaryAnalyzeDGVo.setSalary(nf.format(rs.getBigDecimal("salary")));
				salaryAnalyzeDGVo.setSalaryNum(rs.getBigDecimal("salary"));
				salaryAnalyzeDGVo.setAvgSalary(nf.format(rs.getBigDecimal("avg_salary")));
				salaryAnalyzeDGVo.setAvgSalaryNum(rs.getBigDecimal("avg_salary"));
				salaryAnalyzeDGVo.setIssueYear(rs.getInt("issue_year"));
				salaryAnalyzeDGVo.setIssueMonth(rs.getInt("issue_month"));
				salaryAnalyzeDGVo.setBenefitsNum(BigDecimal.valueOf(0));
				salaryAnalyzeDGVo.setBenefits(nf.format(BigDecimal.valueOf(0)));
				dgVos.add(salaryAnalyzeDGVo);
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}

		sql = "select sum(salary) benefits,issue_year from benefits WHERE user_id = ? group by " + "issue_year limit "
				+ (page - 1) * size + ", " + size;
		rs = (ResultSet) Database.excute(sql, new Object[] { (Object) id });
		try {
			while (rs.next()) {
				for (SalaryAnalyzeDGVo analyzeDGVo : dgVos) {
					if (rs.getInt("issue_year") == analyzeDGVo.getIssueYear()) {
						analyzeDGVo.setBenefitsNum(rs.getBigDecimal("benefits"));
						analyzeDGVo.setBenefits(nf.format(rs.getBigDecimal("benefits")));
					}
					analyzeDGVo.setAvgSalaryWithBenefits(
							nf.format((analyzeDGVo.getSalaryNum().add(analyzeDGVo.getBenefitsNum())).divide(
									BigDecimal.valueOf(analyzeDGVo.getIssueMonth()), 2, BigDecimal.ROUND_HALF_UP)));
					analyzeDGVo.setSalaryWithBenefits(
							nf.format(analyzeDGVo.getSalaryNum().add(analyzeDGVo.getBenefitsNum())));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return dgVos;
	}

	private String handle(String sql, Object[] objects) {
		if (objects != null) {
			for (Object object : objects) {
				sql += object;
			}
		}
		return sql;
	}
}
